#include "databasemanager.h"
#include "robotlist.h"
#include "models/robot.h"
#include"public/public.h"

#include <QFile>
#include <QCryptographicHash>
#include<QDebug>
#include <QJsonDocument>
#include <QJsonArray>
#include <QJsonObject>
#define DB_VERSION 0

DatabaseManager * DatabaseManager::m_pInstance = NULL;
DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent)
{
}

DatabaseManager::~DatabaseManager()
{

}

DatabaseManager * DatabaseManager::getInstance()
{
    if(m_pInstance == NULL)
        m_pInstance = new DatabaseManager();
    return m_pInstance;
}

bool DatabaseManager::initDatabase()
{
    QString strDBPath =getUserPath()+"humanrobot.db";
    QFile file(strDBPath);
    if(!file.exists())
    {
        //数据库文件不存在，复制新的数据库到文件夹
        if(!QFile::copy(getCurrentPath()+"data\\humanrobot.db",strDBPath)) //将文件复制到新的文件路径下
        {
            qDebug()<<"复制 humanrobot.db 失败:"<<getCurrentPath()+"data\\humanrobot.db"<<" => "<<strDBPath;
            return false;
        }
    }

    m_db = QSqlDatabase::addDatabase("QSQLITE","MAINDB");
    m_db.setDatabaseName(strDBPath); //设置数据库名
    if (!m_db.open())
    {
        qDebug()<<"打开 humanrobot.db 失败:"<<m_db.lastError().text();
        return false;
    }
    updataDBtoLeast();

    QSqlQuery query(m_db);
    query.prepare("PRAGMA foreign_keys = ON");
    QString strErr;
    execSql(query,QVariantMap(),strErr);

    return true;
}

bool DatabaseManager::login(QString strUser, QString strPwd, int &nRole, QString &strErr)
{
    QSqlQuery query(m_db);

    query.prepare("select * from user where user=:user");
    QVariantMap param;
    param.insert(":user",strUser);
    if(execSql(query,param,strErr))
    {
        if(query.first())
        {
            QString md5;
            QByteArray pwd = QCryptographicHash::hash(strPwd.toLatin1(), QCryptographicHash::Md5);
            md5.append(pwd.toHex());
            QString p = query.value("pwd").toString();
            if(md5 != p)
            {
                strErr = QObject::tr("密码错误!");
                return false;
            }
            else
            {
                nRole = query.value("role").toInt();
                return true;
            }
        }
        else
        {
            strErr = QObject::tr("用户名不存在!");
            return false;
        }
    }
    return false;
}

bool DatabaseManager::updatePwd(QString strUser, QString strOldPwd, QString strNewPwd, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from user where user=:user");
    QVariantMap param;
    param.insert(":user", strUser);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            QString md5;
            QByteArray pwd = QCryptographicHash::hash(strOldPwd.toLatin1(), QCryptographicHash::Md5);
            md5.append(pwd.toHex());
            QString p = query.value("pwd").toString();
            if(md5 != p)
            {
                strErr = QObject::tr("原密码错误!");
                return false;
            }
            else
            {
                QSqlQuery updateQuery(m_db);
                updateQuery.prepare("update user set pwd=:pwd where user=:user");
                QVariantMap updateParam;
                QString newMd5;
                QByteArray newPwd = QCryptographicHash::hash(strNewPwd.toLatin1(), QCryptographicHash::Md5);
                newMd5.append(newPwd.toHex());
                updateParam.insert(":pwd", newMd5);
                updateParam.insert(":user", strUser);
                return execSql(updateQuery, updateParam, strErr);
            }
        }
        else
        {
            strErr = QObject::tr("用户名不存在!");
            return false;
        }
    }
    return false;
}

bool DatabaseManager::addUser(UserInfo info, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from user where user=:user");
    QVariantMap param;
    param.insert(":user", info.userName);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            strErr = QObject::tr("用户已存在!");
            return false;
        }
        else
        {
            QSqlQuery addQuery(m_db);
            addQuery.prepare("insert into user (user,pwd,role) VALUES (:user,:pwd,:role)");
            QVariantMap addParam;
            QString newMd5;
            QByteArray newPwd = QCryptographicHash::hash(info.userPwd.toLatin1(), QCryptographicHash::Md5);
            newMd5.append(newPwd.toHex());
            addParam.insert(":pwd", newMd5);
            addParam.insert(":user", info.userName);
            addParam.insert(":role", info.nPermission);
            return execSql(addQuery, addParam, strErr);
        }
    }
    return false;
}

bool DatabaseManager::editUser(UserInfo info, QString strOldUserName, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from user where user=:user");
    QVariantMap param;
    param.insert(":user", strOldUserName);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            QSqlQuery updateQuery(m_db);

            QVariantMap updateParam;
            if(info.userPwd!="")
            {
                updateQuery.prepare(tr("update user set user=:user,pwd=:pwd,role=:role where user=%1").arg(strOldUserName));
                QString newMd5;
                QByteArray newPwd = QCryptographicHash::hash(info.userPwd.toLatin1(), QCryptographicHash::Md5);
                newMd5.append(newPwd.toHex());
                updateParam.insert(":pwd", newMd5);
            }
            else
                updateQuery.prepare(tr("update user set user=:user,role=:role where user=%1").arg(strOldUserName));
            updateParam.insert(":user", info.userName);
            updateParam.insert(":role", info.nPermission);
            return execSql(updateQuery, updateParam, strErr);
        }
        else
        {
            strErr = QObject::tr("用户名不存在!");
            return false;
        }
    }
    return false;
}

bool DatabaseManager::deleteUser(QString strUser, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from user where user=:user");
    QVariantMap param;
    param.insert(":user", strUser);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            query.prepare("delete from user where user=:user");
            return execSql(query, param, strErr);
        }
        else
        {
            strErr = QObject::tr("用户名不存在!");
            return false;
        }
    }
    return false;
}

QList<UserInfo> DatabaseManager::getUsers()
{
    QList<UserInfo> listUserInfo;
    QString strErr;
    QSqlQuery query(m_db);
    query.prepare("select * from user where user!= 'admin'");
    QVariantMap param;
    if(execSql(query, param, strErr))
    {
        while (query.next()) {
            UserInfo userInfo;
            userInfo.userName = query.value("user").toString();
            userInfo.userPwd  =  query.value("pwd").toString();
            userInfo.nPermission =  query.value("role").toInt();
            listUserInfo.append(userInfo);
        }
    }
    return listUserInfo;
}

bool DatabaseManager::addWarning(QString robotName, QString warningTime, QString warningText)
{
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO warning_log (robot_name, warning_time, warning_text) VALUES (:robotName, :warningTime, :warningText)");
    QVariantMap param;
    param.insert(":robotName", robotName);
    param.insert(":warningTime", warningTime);
    param.insert(":warningText", warningText);
    QString strErr;
    if(execSql(query, param, strErr))
    {
        return true;
    }
    return false;
}

QList<WarningInfo> DatabaseManager::getWarnings()
{
    QList<WarningInfo> lWarningInfo;
    QString strErr;
    QSqlQuery query(m_db);
    query.prepare("select * from warning_log");
    QVariantMap param;
    if(execSql(query, param, strErr))
    {
        while (query.next()) {
            WarningInfo warningInfo;
            warningInfo.robotName = query.value("robot_name").toString();
            warningInfo.warningTime  =  query.value("warning_time").toString();
            warningInfo.warningText =  query.value("warning_text").toString();
            lWarningInfo.append(warningInfo);
        }
    }
    return lWarningInfo;
}

bool DatabaseManager::deleteWarning()
{
    Robot *pCurRobot=RobotList::getInstance()->getCurRobot();
    if(!pCurRobot)
        return false;
    QString strErr;
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM warning_log where robot_name=:robot_name");
    QVariantMap mpParam;
    mpParam.insert(":robot_name", pCurRobot->strName());
    if(execSql(query, mpParam, strErr)) {
        return true;
    }
    return false;
}

bool DatabaseManager::getRobotList(QList<Robot *> &lsRobot, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from robot");
    lsRobot.clear();
    if(execSql(query,QVariantMap(),strErr))
    {
        while (query.next()) {
            QString strVideo = query.value("video_url").toString();
            QList<VideoInfo>lsVideo;
            QJsonDocument doc = QJsonDocument::fromJson(strVideo.toUtf8());
            if(doc.isArray()) {
                QJsonArray array = doc.array();
                for(const QJsonValue &value : array) {
                    QJsonObject obj = value.toObject();
                    lsVideo.push_back(VideoInfo{obj["name"].toString(),obj["url"].toString()});
                }
            }
            lsRobot.append(new Robot(query.value("id").toInt(),
                                     query.value("name").toString(),
                                     query.value("urdf").toString(),
                                     query.value("sport_ip").toString(),
                                     query.value("sport_port").toInt(),
                                     query.value("perception_ip").toString(),
                                     query.value("perception_port").toInt(),
                                     query.value("slam_ip").toString(),
                                     query.value("slam_port").toInt(),
                                     query.value("speak_ip").toString(),
                                     query.value("speak_port").toInt(),
                                     query.value("zero_ip").toString(),
                                     query.value("zero_port").toInt(),
                                     query.value("telemetry_ip").toString(),
                                     query.value("telemetry_port").toInt(),
                                     (ToolType)query.value("left_tool").toInt(),
                                     (ToolType)query.value("right_tool").toInt(),
                                     (HeadType)query.value("head_type").toInt(),
                                     query.value("user").toString(),
                                     query.value("password").toString(),
                                     lsVideo));

        } 
        return true;
    }
    return false;
}

bool DatabaseManager::newRobot(QString strName,
                               QString strUrdfName,
                               QString strSportIP,
                               int nSportPort,
                               QString strPerceptionIP,
                               int nPerceptionrPort,
                               QString strSlamIP,
                               int nSlamPort,
                               QString strSpeakIP,
                               int nSpeakPort,
                               QString strZeroIP,
                               int nZeroPort,
                               QString strTelemetryIP,
                               int nTelemetryPort,
                               int nLeftTool,
                               int nRightTool,
                               int nHeadType,
                               QString strUser,
                               QString strPwd,
                               QList<VideoInfo>lsVideo,
                               int & nID,
                               QString &strErr)
{
    QSqlQuery query(m_db);
    QString strVideo;
    QJsonArray array;
    for(auto it = lsVideo.begin(); it != lsVideo.end(); ++it) {
        QJsonObject obj;
        obj["name"] = it->strName;
        obj["url"] = it->strUrl;
        array.append(obj);
    }
    QJsonDocument doc(array);
    strVideo = QString::fromUtf8(doc.toJson(QJsonDocument::Compact));

    query.prepare("INSERT INTO robot (name,urdf,sport_ip, sport_port,perception_ip,perception_port,zero_ip,zero_port,telemetry_ip,telemetry_port,slam_ip,slam_port,speak_ip,speak_port,left_tool,right_tool,head_type,user,password,video_url) VALUES (:name, :urdf, :sport_ip, :sport_port,:perception_ip,:perception_port,:zero_ip,:zero_port,:telemetry_ip,:telemetry_port,:slam_ip,:slam_port,:speak_ip,:speak_port,:left_tool,:right_tool,:head_type,:user,:password,:video_url)");
    QVariantMap mpParam;
    mpParam.insert(":name", strName);
    mpParam.insert(":urdf", strUrdfName);
    mpParam.insert(":sport_ip", strSportIP);
    mpParam.insert(":sport_port", nSportPort);
    mpParam.insert(":perception_ip", strPerceptionIP);
    mpParam.insert(":perception_port", nPerceptionrPort);
    mpParam.insert(":zero_ip", strZeroIP);
    mpParam.insert(":zero_port", nZeroPort);
    mpParam.insert(":telemetry_ip", strTelemetryIP);
    mpParam.insert(":telemetry_port", nTelemetryPort);
    mpParam.insert(":slam_ip", strSlamIP);
    mpParam.insert(":slam_port", nSlamPort);
    mpParam.insert(":speak_ip", strSpeakIP);
    mpParam.insert(":speak_port", nSpeakPort);
    mpParam.insert(":left_tool", nLeftTool);
    mpParam.insert(":right_tool", nRightTool);
    mpParam.insert(":head_type", nHeadType);
    mpParam.insert(":user", strUser);
    mpParam.insert(":password", strPwd);
    mpParam.insert(":video_url", strVideo);
    if(execSql(query, mpParam, strErr)) {
        nID = query.lastInsertId().toInt();
        return true;
    }
    return false;
}

bool DatabaseManager::updateRobot(int nID,
                                  QString strName,
                                  QString strUrdfName,
                                  QString strSportIP,
                                  int nSportPort,
                                  QString strPerceptionIP,
                                  int nPerceptionrPort,
                                  QString strSlamIP,
                                  int nSlamPort,
                                  QString strSpeakIP,
                                  int nSpeakPort,
                                  QString strZeroIP,
                                  int nZeroPort,
                                  QString strTelemetryIP,
                                  int nTelemetryPort,
                                  int nLeftTool,
                                  int nRightTool,
                                  int nHeadType,
                                  QString strUser,
                                  QString strPwd,
                                  QList<VideoInfo>lsVideo,
                                  QString &strErr)
{
    QSqlQuery query(m_db);
    QString strVideo;

    QJsonArray array;
    for(auto it = lsVideo.begin(); it != lsVideo.end(); ++it) {
        QJsonObject obj;
        obj["name"] = it->strName;
        obj["url"] = it->strUrl;
        array.append(obj);
    }
    QJsonDocument doc(array);
    strVideo = QString::fromUtf8(doc.toJson(QJsonDocument::Compact));
    query.prepare("UPDATE robot SET name=:name,urdf=:urdf, sport_ip=:sport_ip,sport_port=:sport_port, perception_ip=:perception_ip,perception_port=:perception_port, zero_ip=:zero_ip,zero_port=:zero_port, telemetry_ip=:telemetry_ip,telemetry_port=:telemetry_port, slam_ip=:slam_ip,slam_port=:slam_port, speak_ip=:speak_ip,speak_port=:speak_port,left_tool=:left_tool,right_tool=:right_tool,head_type=:head_type,user=:user,password=:password,video_url=:video_url WHERE id=:id");
    QVariantMap mpParam;
    mpParam.insert(":id", nID);
    mpParam.insert(":name", strName);
    mpParam.insert(":urdf", strUrdfName);
    mpParam.insert(":sport_ip", strSportIP);
    mpParam.insert(":sport_port", nSportPort);
    mpParam.insert(":perception_ip", strPerceptionIP);
    mpParam.insert(":perception_port", nPerceptionrPort);
    mpParam.insert(":zero_ip", strZeroIP);
    mpParam.insert(":zero_port", nZeroPort);
    mpParam.insert(":telemetry_ip", strTelemetryIP);
    mpParam.insert(":telemetry_port", nTelemetryPort);
    mpParam.insert(":slam_ip", strSlamIP);
    mpParam.insert(":slam_port", nSlamPort);
    mpParam.insert(":speak_ip", strSpeakIP);
    mpParam.insert(":speak_port", nSpeakPort);
    mpParam.insert(":left_tool", nLeftTool);
    mpParam.insert(":right_tool", nRightTool);
    mpParam.insert(":head_type", nHeadType);
    mpParam.insert(":user", strUser);
    mpParam.insert(":password", strPwd);
    mpParam.insert(":video_url", strVideo);
    if(execSql(query, mpParam, strErr)) {
        return true;
    }
    return false;
}


bool DatabaseManager::deleteRobot(int nID, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM robot WHERE id=:id");
    QVariantMap mpParam;
    mpParam.insert(":id", nID);
    if(execSql(query, mpParam, strErr)) {
        return true;
    }
    return false;
}

void DatabaseManager::setLsWnd(int nRobotID, const QList<int> &newLsWnd)
{
    QJsonArray jsonArray;
    for (int wnd : newLsWnd) {
        jsonArray.append(wnd);
    }
    QJsonDocument jsonDoc(jsonArray);
    QString strJson = QString::fromUtf8(jsonDoc.toJson(QJsonDocument::Compact));

    QSqlQuery query(m_db);
    query.prepare("UPDATE robot SET show_wnd=:show_wnd WHERE id=:id");
    QVariantMap param;
    param.insert(":show_wnd", strJson);
    param.insert(":id", nRobotID);
    QString strErr;
    execSql(query, param, strErr);
}

//视频为下标+1，1001--rudf    1002---感知
QList<int> DatabaseManager::getLsWnd(int nRobotID)
{
    QList<int> lsWnd;
    QSqlQuery query(m_db);
    query.prepare("SELECT show_wnd FROM robot WHERE id=:id");
    QVariantMap param;
    param.insert(":id", nRobotID);
    QString strErr;
    if(execSql(query, param, strErr)) {
        if(query.next()) {
            QString strJson = query.value("show_wnd").toString();
            QJsonDocument jsonDoc = QJsonDocument::fromJson(strJson.toUtf8());
            if(jsonDoc.isArray()) {
                QJsonArray jsonArray = jsonDoc.array();
                for (const QJsonValue &value : jsonArray) {
                    lsWnd.append(value.toInt());
                }
            }
        }
    }
    return lsWnd;
}

bool DatabaseManager::loadConfig(bool &bShowVritualBtn,bool &bShowJoysticBtn, QString & strTelemetryFolder, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("SELECT * FROM system_setting");
    QVariantMap mpParam;
    if(execSql(query, mpParam, strErr)) {
        if(query.next()) {
            bShowVritualBtn = query.value("show_virtual_btns").toBool();
            strTelemetryFolder = query.value("telemetry_folder").toString();
            bShowJoysticBtn = query.value("show_joystick_btns").toBool();
            return true;
        } else {
            strErr = QObject::tr("获取配置失败!");
            bShowVritualBtn = false;
            bShowVritualBtn=false;
            strTelemetryFolder = "/temp/data";
            return false;
        }
    }
    return false;
}

bool DatabaseManager::setConfig(bool bShowVritualBtn,bool bShowJoystickBtn, QString strTelemetryFolder, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("UPDATE system_setting SET show_virtual_btns=:show_virtual_btns,telemetry_folder=:telemetry_folder,show_joystick_btns=:show_joystick_btns");
    QVariantMap mpParam;
    mpParam.insert(":show_virtual_btns", bShowVritualBtn);
    mpParam.insert(":telemetry_folder", strTelemetryFolder);
    mpParam.insert(":show_joystick_btns", bShowJoystickBtn);
    if(execSql(query, mpParam, strErr)) {
        return true;
    }
    return false;
}



bool DatabaseManager::getUpperLimbCmd(QList<Command> &lsCmd, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from upper_limb_cmd");
    if(execSql(query, QVariantMap(), strErr))
    {
        while (query.next()) {
            Command cmd;
            cmd.nID = query.value("id").toInt();
            cmd.nHexCmd = query.value("hex_cmd").toInt();
            cmd.nFuncKey = (FUNCTION_KEY)query.value("fun_key").toInt();
            cmd.nHotKey = (STICK_KEY)query.value("hot_key").toInt();
            cmd.strShowText = query.value("show_text").toString();
            cmd.strFile = query.value("file").toString();
            cmd.nParentID = query.value("parent_id").toInt();
            lsCmd.append(cmd);
        }
        return true;
    }
    return false;
}

bool DatabaseManager::updateUpperLimbCmd(QList<Command> &lsCmd, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM upper_limb_cmd");
    if(!execSql(query, QVariantMap(), strErr))
    {
        return false;
    }
    for(int i = 0; i < lsCmd.size(); ++i)
    {
        query.prepare("INSERT INTO upper_limb_cmd (id,hex_cmd, fun_key,hot_key,show_text,file,parent_id) VALUES (:id,:hex_cmd,:fun_key,:hot_key,:show_text,:file,:parent_id)");
        QVariantMap mpParam;
        mpParam.insert(":id", lsCmd[i].nID);
        mpParam.insert(":hex_cmd", lsCmd[i].nHexCmd);
        mpParam.insert(":hex_cmd", lsCmd[i].nHexCmd);
        mpParam.insert(":fun_key", lsCmd[i].nFuncKey);
        mpParam.insert(":hot_key", lsCmd[i].nHotKey);
        mpParam.insert(":show_text", lsCmd[i].strShowText);
        mpParam.insert(":file", lsCmd[i].strFile);
        mpParam.insert(":parent_id", lsCmd[i].nParentID);
        execSql(query, mpParam, strErr);
    }
    return true;
}

bool DatabaseManager::getGlobalCmd(QList<Command> &lsCmd, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from global_limb_cmd");
    if(execSql(query, QVariantMap(), strErr))
    {
        while (query.next()) {
            Command cmd;
            cmd.nID = query.value("id").toInt();
            cmd.nHexCmd = query.value("hex_cmd").toInt();
            cmd.nFuncKey = (FUNCTION_KEY)query.value("fun_key").toInt();
            cmd.nHotKey = (STICK_KEY)query.value("hot_key").toInt();
            cmd.strShowText = query.value("show_text").toString();
            cmd.nParentID = query.value("parent_id").toInt();
            lsCmd.append(cmd);
        }
        return true;
    }
    return false;
}

bool DatabaseManager::updateGlobalCmd(QList<Command> &lsCmd, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM global_limb_cmd");
    if(!execSql(query, QVariantMap(), strErr))
    {
        return false;
    }
    for(int i = 0; i < lsCmd.size(); ++i)
    {
        query.prepare("INSERT INTO global_limb_cmd (id,hex_cmd,fun_key,hot_key,show_text,parent_id) VALUES (:id,:hex_cmd,:fun_key,:hot_key,:show_text,:parent_id)");
        QVariantMap mpParam;
        mpParam.insert(":id", lsCmd[i].nID);
        mpParam.insert(":hex_cmd", lsCmd[i].nHexCmd);
        mpParam.insert(":fun_key", lsCmd[i].nFuncKey);
        mpParam.insert(":hot_key", lsCmd[i].nHotKey);
        mpParam.insert(":show_text", lsCmd[i].strShowText);
        mpParam.insert(":parent_id", lsCmd[i].nParentID);
        if(execSql(query, mpParam, strErr))
            lsCmd[i].nID = query.lastInsertId().toInt();
        else
            return false;
    }
    return true;
}

bool DatabaseManager::getAudioFile(QList<AudioFile> &lsFile, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from audio_file");
    if(execSql(query, QVariantMap(), strErr))
    {
        while (query.next()) {
            AudioFile file;
            file.nID = query.value("id").toInt();
            file.strShowText = query.value("show_text").toString();
            file.strAudioFile = query.value("audio_file").toString();
            file.strActionFile = query.value("action_file").toString();
            file.nFuncKey = (FUNCTION_KEY)query.value("fun_key").toInt();
            file.nHotKey = (STICK_KEY)query.value("hot_key").toInt();
            lsFile.append(file);
        }
        return true;
    }
    return false;
}

bool DatabaseManager::updateAudioFile(QList<AudioFile> &lsFile, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("DELETE FROM audio_file");
    if(!execSql(query, QVariantMap(), strErr))
    {
        return false;
    }
    for(int i = 0; i < lsFile.size(); ++i)
    {
        query.prepare("INSERT INTO audio_file(id,show_text,audio_file,action_file,fun_key,hot_key) VALUES (:id,:show_text,:audio_file,:action_file,:fun_key,:hot_key)");
        QVariantMap mpParam;
        mpParam.insert(":id", lsFile[i].nID);
        mpParam.insert(":show_text", lsFile[i].strShowText);
        mpParam.insert(":audio_file", lsFile[i].strAudioFile);
        mpParam.insert(":action_file", lsFile[i].strActionFile);
        mpParam.insert(":fun_key", lsFile[i].nFuncKey);
        mpParam.insert(":hot_key", lsFile[i].nHotKey);
        execSql(query, mpParam, strErr);
    }
    return true;
}

bool DatabaseManager::addTeach(TeachInfo info, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from teach where teach_name=:teach_name");
    QVariantMap param;
    param.insert(":teach_name", info.teachName);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            strErr = QObject::tr("示教名称已存在!");
            return false;
        }
        else
        {
            QSqlQuery addQuery(m_db);
            addQuery.prepare("insert into teach (robot_id,teach_name,teach_data) VALUES (:robot_id,:teach_name,:teach_data)");
            QVariantMap addParam;
            addParam.insert(":robot_id", info.nRobotID);
            addParam.insert(":teach_name", info.teachName);
            addParam.insert(":teach_data", info.teachJson);
            return execSql(addQuery, addParam, strErr);
        }
    }
    return false;
}

bool DatabaseManager::editTeach(TeachInfo info, QString strOldTeachName, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare(tr("select * from teach where teach_name='%1'").arg(strOldTeachName));

    if(execSql(query, QVariantMap(), strErr))
    {
        if(query.first())
        {
            QSqlQuery updateQuery(m_db);
            QVariantMap updateParam;

            updateQuery.prepare(tr("update teach set teach_name=:teach_name,teach_data=:teach_data where teach_name='%1'").arg(strOldTeachName));
            updateParam.insert(":teach_name", info.teachName);
            updateParam.insert(":teach_data", info.teachJson);
            return execSql(updateQuery, updateParam, strErr);
        }
        else
        {
            strErr = QObject::tr("示教不存在!");
            return false;
        }
    }
    return false;
}

bool DatabaseManager::deleteTeach(QString strTeachName, QString &strErr)
{
    QSqlQuery query(m_db);
    query.prepare("select * from teach where teach_name=:teach_name");
    QVariantMap param;
    param.insert(":teach_name", strTeachName);
    if(execSql(query, param, strErr))
    {
        if(query.first())
        {
            query.prepare("delete from teach where teach_name=:teach_name");
            return execSql(query, param, strErr);
        }
        else
        {
            strErr = QObject::tr("示教不存在!");
            return false;
        }
    }
    return false;
}

bool DatabaseManager::isTeachNameExist(QString strTeachName)
{
    QSqlQuery query(m_db);
    query.prepare(QString("select * from teach where 'teach_name'='%1'").arg(strTeachName));
    QString strErr;
    if(execSql(query, QVariantMap(), strErr))
    {
        if(query.first())
            return true;
        else
            return false;
    }
    return false;
}

QList<TeachInfo> DatabaseManager::getTeach(int nRobotID)
{
    QSqlQuery query(m_db);
    QList<TeachInfo> teachList;
    query.prepare(QString("select * from teach where robot_id=%1").arg(nRobotID));
    QString strErr;
    if(execSql(query, QVariantMap(), strErr))
    {
        while (query.next()) {
            TeachInfo info;
            info.nId = query.value("id").toInt();
            info.nRobotID = query.value("robot_id").toInt();
            info.teachName = query.value("teach_name").toString();
            info.teachJson = query.value("teach_data").toString();
            info.vTeachData = JsonToteachData(info.teachJson);
            teachList.append(info);
        }
    }
    return teachList;
}

QVector<TeachData> DatabaseManager::JsonToteachData(QString json)
{
    QVector<TeachData> vTeachData;
    // 解析JSON数据
    QJsonParseError parseError;
    QJsonDocument jsonDoc = QJsonDocument::fromJson(json.toUtf8(), &parseError);
    QJsonObject jsonObj = jsonDoc.object();
    for (int i = 0; i < jsonObj.count(); ++i) {
        TeachData teach;
        QJsonArray arr=jsonObj["node"+QString::number(i+1)].toArray();
        for(int j=0;j<arr.size();j++)
        {
            if(j<31)
                teach.jointData.append(arr[j].toDouble());
            else if(j==31)
                teach.execTime=arr[31].toInt();
            else
                teach.waitTime=arr[32].toInt();
        }
        vTeachData.append(teach);
    }
    return vTeachData;
}
bool DatabaseManager::execSql(QSqlQuery &query, QVariantMap mpParam, QString &strErr)
{
    QString strSql = query.lastQuery();
    for(QVariantMap::Iterator it = mpParam.begin(); it != mpParam.end(); ++it)
    {
        strSql.replace(it.key(),it.value().toString());
        query.bindValue(it.key(),it.value().toString());
    }
    bool bRet = query.exec();
    if(!bRet)
    {
        qDebug()<<"执行sql语句失败 "<<query.lastError().text()<<" : "<<strSql;
        strErr = query.lastError().text();
    }
    else
    {
        qDebug()<<"执行sql语句成功 "<<strSql;
    }
    return bRet;
}

void DatabaseManager::updataDBtoLeast()
{
    int nCurVersion = 0;
    QSqlQuery query(m_db);
    query.prepare("select * from db_version");
    QString strErr = "";
    if(execSql(query,QVariantMap(),strErr))
    {
        if(query.next())
        {
            nCurVersion = query.value("version").toInt();
        }
    }

    qDebug()<<"当前数据库版本:"<<nCurVersion;
    if(nCurVersion < DB_VERSION)
    {
        for(int i = nCurVersion+1; i <= DB_VERSION; ++i)
        {
            qDebug()<<QString("%1/data/humanrobot_%2_%3.sql").arg(getCurrentPath()).arg(i-1).arg(i);
            QFile qfile(QString("%1/data/humanrobot_%2_%3.sql").arg(getCurrentPath()).arg(i-1).arg(i));
            if (!qfile.exists()) {
                continue;
            }
            if (!qfile.open(QIODevice::ReadOnly | QIODevice::Text)) {
                continue;
            }
            QTextStream in(&qfile);
            in.setCodec("UTF-8");
            QString qstr_file_data = in.readAll();
            QStringList qstrlist_sql =  qstr_file_data.split(";");
            for (int i = 0; i < qstrlist_sql.size() - 1; i++) {
                QString qstr_sql_part = qstrlist_sql.at(i).toUtf8();
                query.prepare(qstr_sql_part);
                execSql(query,QVariantMap(), strErr);
            }
        }
    }
}
